<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <title>Import từ file excel</title>
        <style type="text/css">
        </style><link href="css/style.css" rel="stylesheet" type="text/css" />
        <!--<link href="../SpryAssets/SpryMenuBarHorizontal.css" rel="stylesheet" type="text/css" />
        <script src="../SpryAssets/SpryMenuBar.js" type="text/javascript"></script>-->
    </head>
    <body>
        <form action="" method="post" enctype="multipart/form-data" name="form1">
            <p>&nbsp;</p>
            <table width="715" border="0" align="center" cellspacing="0">
                <tr>
                    <th height="23" colspan="5" class="TableHeader" scope="col">Thêm máy biến áp từ file Excel</th>
                </tr>
                <tr class="TableField">
                    <td width="83">&nbsp;</td>
                    <td width="322"><input type="file" name="file" id="file" /></td>
                    <td width="208"><input type="submit" name="upload" id="upload" value="Upload từ file excel" /></td>
                    <td width="118">&nbsp;</td>
                    <td width="16">&nbsp;</td>
                </tr>
            </table>
            <p>&nbsp;</p>
            <p>&nbsp;</p>
            <p>&nbsp;</p>
        </form>
        <?php
        include_once($_SERVER['DOCUMENT_ROOT'] . '/mba/models/MBA.php');
        include_once($_SERVER['DOCUMENT_ROOT'] . '/mba/PHPExcel_1.7.9_doc/Classes/PHPExcel.php');
        include_once($_SERVER['DOCUMENT_ROOT'] . '/mba/PHPExcel_1.7.9_doc/Classes/PHPExcel/IOFactory.php');
        $sodongthemvao = 0;
        $sodongloi = 0;

        if (isset($_POST['upload'])) {
            if ($_FILES["file"]["name"] != "") {

                $objPHPExcel = PHPExcel_IOFactory::load($_FILES["file"]["tmp_name"]);
                $objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
                foreach ($objWorksheet->getRowIterator() as $row) {
                    $worksheetTitle = $objWorksheet->getTitle();
                    $highestRow = $objWorksheet->getHighestRow(); // e.g. 10
                    $highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'
                    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
                }
//
                $nrColumns = $highestColumnIndex;
// hien thong tin file excel
                /* echo "File :".$worksheetTitle." has ";
                  echo $nrColumns . ' columns';
                  echo ' y ' . $highestRow . ' rows.'; */
                echo " <a class='DataDetail' >";
                echo "File :" . $worksheetTitle . " có  ";
                echo ' ' . $highestRow - 3 . ' dòng dử liệu máy biến áp.';
                echo "<br>";
                echo "Data:";
                echo "<br>";
                echo " </a>";  // hien thi ra bang thong bao cac may import
                echo ' <table cellpadding="3" cellspacing="1" border="0" align="center" width="3500"><tr>';
// header cua bang hien thi
                echo "<tr>";
                echo "<th rowspan='2' class='TableHeader'>Kết quả Import</th>";
                echo "<th rowspan='2' class='TableHeader'>Số N0</th>";
                echo "    <th rowspan='2' class='TableHeader' >MSTS</th>";
                echo "     <th rowspan='2' class='TableHeader' width='100'>Loại điện áp (U)</th>";
                echo "     <th rowspan='2' class='TableHeader' width='100'>Công suất (S)</th>";
                echo "      <th rowspan='2' class='TableHeader' width='100'>Chiều dài (m)</th>";
                echo "   <th rowspan='2' class='TableHeader' width='100'>Chiều rộng (m)</th>";
                echo "   <th rowspan='2' class='TableHeader' width='100'>Chiều cao (m)</th>";
                echo "  <th rowspan='2' class='TableHeader' width='100'>Dầu máy (kg)</th>";
                echo "   <th rowspan='2' class='TableHeader' width='100'>Ruột máy (kg)</th>";
                echo "  <th rowspan='2' class='TableHeader' width='50'>Tổng (kg)</th>";
                echo "  <th rowspan='2' class='TableHeader'>Nhà SX</th>";
                echo "  <th rowspan='2' class='TableHeader'>Nước sản xuất</th>";
                echo "   <th rowspan='2' class='TableHeader' width='50'>Năm sản xuất</th>";
                echo "    <th rowspan='2' class='TableHeader'>Tên trạm</th>";
                echo "    <th rowspan='2' class='TableHeader'>Ngày vận hành</th>";
                echo " <th rowspan='2' class='TableHeader' width='50'>Năm nhập về</th>";
                echo "    <th colspan='7' class='TableHeader'>Loại dầu</th>";
                echo "   <th colspan='2' class='TableHeader'>Loại máy</th>";
                echo "  <th rowspan='2' class='TableHeader'>Tình trạng máy</th>";
                echo "  <th rowspan='2' class='TableHeader'>Có tình trạng</th>";
                echo " <th rowspan='2' class='TableHeader'>Diễn giải tình trạng</th>";
                echo "   <th rowspan='2' class='TableHeader'>Thông số đo</th>";
                echo "   <th rowspan='2' class='TableHeader'>Nhận xét</th>";
                echo "  <th rowspan='2' class='TableHeader' width='200'>Ghi chú</th>";
                echo "<th rowspan='2' class='TableHeader'>Ngày ngưng</th>";
                echo "  <th rowspan='2' class='TableHeader'>Ngày đại tu</th>";
                echo "  <th rowspan='2' class='TableHeader'>Tên đơn vị</th>";
                echo "  <th rowspan='2' class='TableHeader'>Ngày chuyển đến</th>";
                echo " </tr>";

                echo " <tr>";
                echo "  <td class='TableHeader'>Castrol</td>";
                echo "  <td class='TableHeader'>Nynas</td>";
                echo " <td class='TableHeader'>DIALA_A</td>";
                echo " <td class='TableHeader'>DIALA-AX</td>";
                echo " <td class='TableHeader'>APBLUE</td>";
                echo " <td class='TableHeader'>SUPER-T</td>";
                echo " <td class='TableHeader'>Loại dầu khác</td>";
                echo "<td class='TableHeader'>1 pha</td>";
                echo " <td class='TableHeader'>3 pha</td>";
                echo "  </tr>";



// hien thi thong tin bang excel
                for ($row = 4; $row <= $highestRow; ++$row) {

                    // bat dau lay du lieu tu file excel hien len
                    echo '<tr>';
                    // truoc moi dong du lieu them vao ket qua import
                    $ketquaip = "ketqua";


                    $MBA_TEST = new MBA();
                    $cell_test = $objWorksheet->getCellByColumnAndRow(0, $row);
                    $SO_N0_TEST = $cell_test->getValue();
                    $MBA_TEST->setSO_N0($SO_N0_TEST);
                    if ($MBA_TEST->isExits("may_bien_ap", "SO_N0='" . $MBA_TEST->getSO_N0() . "'") <= 0) {
                        $ketquaip = " OK !";
                        $sodongthemvao++;
                    } else {
                        $ketquaip = " MBA đã tồn tại !";
                        $sodongloi++;
                    }

                    if ($ketquaip == " OK !") {

                        if ($row % 2 == 0) {
                            echo '<td align="center" class="TableField2" > ' . $ketquaip . '</td>';
                        } else {
                            echo '<td align="center" class="TableField" > ' . $ketquaip . '</td>';
                        }
                    } else {
                        if ($row % 2 == 0) {
                            echo '<td align="center" class="TableField2error" > ' . $ketquaip . '</td>';
                        } else {
                            echo '<td align="center" class="TableFielderror" > ' . $ketquaip . '</td>';
                        }
                    }



                    // in du lieu tu file excel
                    for ($col = 0; $col < $nrColumns; ++$col) {
                        $cell = $objWorksheet->getCellByColumnAndRow($col, $row);
                        $val = $cell->getValue();


                        if ($row % 2 == 0) {
                            echo '<td align="center" class="TableField2" > ' . $val . '</td>';
                        } else {
                            echo '<td align="center" class="TableField" > ' . $val . '</td>';
                        }
                    }
                    echo '</tr>';
                }
                echo '</table>';





// quang vao csdl
                for ($row = 4; $row <= $highestRow; ++$row) {
                    $val = array();
                    for ($col = 0; $col < $nrColumns; $col++) {
                        $cell = $objWorksheet->getCellByColumnAndRow($col, $row);
                        $val[] = $cell->getValue();
                    }

                    // $sql="insert into tablename(column1, column2, column3, column4, column5, column6)
                    // values('".$val[1] . "','" . $val[2] . "','" . $val[3]. "','" . $val[4]. "','" . $val[5]. "','" . $val[6]. "')";
//Run your mysql_query
                    $MBA = new MBA();

                    // thong tin chinh - bang may_bien_ap

                    $SO_N0 = $val[0];
                    $MSTS = $val[1];
                    $CONG_SUAT = $val[3];
                    $LOAI_DIEN_AP = $val[2];
                    $MA_DIEN_AP = "";
                    // do MA_DIEN_AP tu LOAI_DIEN_AP trong CSDL
                    $db = new database();
                    $db->setQuery("select * from loai_dien_ap");
                    $result = $db->fetchAll();
                    while ($rows = mysql_fetch_array($result)) {
                        if ($LOAI_DIEN_AP == $rows['CAP_DIEN_AP']) {
                            $MA_DIEN_AP = $rows['MA_DIEN_AP'];
                        }
                    }

                    // kiem tra xem MA_DIEN_AP co trong csdl chua, neu chua them vao
                    if ($MA_DIEN_AP == "") {
                        // tang MA_DIEN_AP trong CSDL len 1
                        $db = new database();
                        $db->setQuery("SELECT MAX(MA_DIEN_AP) FROM loai_dien_ap");
                        $result = $db->fetchAll();
                        while ($rows = mysql_fetch_array($result)) {
                            $MA_DIEN_AP = $rows['MAX(MA_DIEN_AP)'];
                        }

                        $MA_DIEN_AP++;


                        //them MA_DIEN_AP + CAP_DIEN_AP moi VAO CSDL
                        // them vao bang loai_dien_ap ----------------------------------------------------------------
                        $db = new database();
                        $db->setQuery("insert into loai_dien_ap(MA_DIEN_AP,CAP_DIEN_AP) 
            values ('" . $MA_DIEN_AP . "','" . $LOAI_DIEN_AP . "')");
                        $db->executeQuery();
                    }

                    // NUOC SX
                    $TEN_NUOC = $val[11];

                    // KIEM TRA TEN_NUOC CO TON TAI TRONG BANG NUOC_SX KO, NEU CHUA THI THEM VAO
                    $db = new database();
                    $db->setQuery("select * from nuoc_sx");
                    $result = $db->fetchAll();
                    while ($rows = mysql_fetch_array($result)) {
                        if ($$TEN_NUOC == $rows['TEN_NUOC_SX']) { // khong lam gi ca 
                        } else {
                            // them ten nuoc moi vao csdl
                            $db = new database();
                            $db->setQuery("insert into nuoc_sx(TEN_NUOC_SX) 
           				 values ('" . $TEN_NUOC . "')");
                            $db->executeQuery();
                        }
                    }
                    // tu file excel la NHASX ~> MA_NHA_SX
                    $TEN_NSX = $val[10];
                    $MA_NSX = "";
                    $db = new database();
                    $db->setQuery("select * from nha_sx");
                    $result = $db->fetchAll();
                    while ($rows = mysql_fetch_array($result)) {
                        if ($TEN_NSX == $rows['TEN_NSX']) {
                            $MA_NSX = $rows['MA_NSX'];
                        }
                    }

                    // kiem tra ten nha san sx co trong he thong chua . neu khon thi them vao
                    if ($MA_NSX == "") {
                        // tang MA_DIEN_AP trong CSDL len 1
                        $db = new database();
                        $db->setQuery("SELECT MAX(MA_NSX) FROM nha_sx");
                        $result = $db->fetchAll();
                        while ($rows = mysql_fetch_array($result)) {
                            $MA_NSX = $rows['MAX(MA_NSX)'];
                        }

                        $MA_NSX++;


                        //them MA_DIEN_AP + CAP_DIEN_AP moi VAO CSDL
                        // them vao bang loai_dien_ap ----------------------------------------------------------------
                        $db = new database();
                        $db->setQuery("insert into nha_sx(MA_NSX,TEN_NSX,TEN_NUOC) 
            values ('" . $MA_NSX . "','" . $TEN_NSX . "','" . $TEN_NUOC . "')");
                        $db->executeQuery();
                    }





                    // loai may' 
                    if ($val[23] == "x") {
                        $MA_LOAI = 1;
                    }
                    if ($val[24] == "x") {
                        $MA_LOAI = 2;
                    }

                    $CHIEU_DAI = $val[4];
                    $CHIEU_RONG = $val[5];
                    $CHIEU_CAO = $val[6];
                    $DAU_MAY = $val[7];
                    $RUOT_MAY = $val[8];
                    $TONG = $val[9];
                    $NAM_SX = $val[12];
                    $NAM_NHAP_VE = $val[15];

                    $THONG_SO_DO = $val[28];
                    $NHAN_XET = $val[29];
                    $GHI_CHU = $val[30];

                    // Thong tin cua bang chi_tiet_dai_tu
                    $NGAY_DAI_TU = $val[32];
                    // bang chi_tiet_ngung_van_hanh
                    $NGAY_NGUNG_VH = $val[31];
                    // bang chi_tiet_tt_may
                    $TEN_TINH_TRANG_MAY = $val[25];
                    $TINH_TRANG_NGAY = $val[26];
                    $DG_TINH_TRANG = $val[27];

                    // KIEM TRA TEN TINH TRANG CO TRONG CSDL CHUA- NEU CHUA THI THEM VAO CSDL
                    $testtinhtrang = 0;
                    $db = new database();
                    $db->setQuery("select * from tinh_trang_may");
                    $result = $db->fetchAll();
                    while ($rows = mysql_fetch_array($result)) {
                        if ($TEN_TINH_TRANG_MAY == $rows['TEN_TINH_TRAN_MAY']) {
                            // khong lam gi ca 
                        } else {
                            $testtinhtrang = 1;
                        }
                    }
                    
                    if ($testtinhtrang == 1) {
                        // them vao ban tinh_trang_may-> ten tinh trang moi
                        $db = new database();
                        $db->setQuery("insert into tinh_trang_may(TEN_TINH_TRANG_MAY) 
            values ('" . $TEN_TINH_TRANG_MAY . "')");
                        $db->executeQuery();
                    }
                    
                    // bang loai_dau
                    $MA_LOAI_DAU = "";
                    if ($val[16] == "x") {
                        $MA_LOAI_DAU = 1;
                    }
                    if ($val[17] == "x") {
                        $MA_LOAI_DAU = 2;
                    }
                    if ($val[18] == "x") {
                        $MA_LOAI_DAU = 3;
                    }
                    if ($val[19] == "x") {
                        $MA_LOAI_DAU = 4;
                    }
                    if ($val[20] == "x") {
                        $MA_LOAI_DAU = 5;
                    }
                    if ($val[21] == "x") {
                        $MA_LOAI_DAU = 6;
                    }
                    $TEN_LOAI_DAU = $val[22];
                    if ($MA_LOAI_DAU == "") {
                        // kiem tra TEN_LOAI_DAU moi co trong CSDL chua -> neu co thi lay MA_LOAI_DAU tuong ung
                        $db = new database();
                        $db->setQuery("SELECT * FROM loai_dau");
                        $result = $db->fetchAll();
                        while ($rows = mysql_fetch_array($result)) {
                            if ($TEN_LOAI_DAU == $rows['TEN_LOAI_DAU']) {
                                $MA_LOAI_DAU = $rows['MA_LOAI_DAU'];
                            }
                        }

                        if ($MA_LOAI_DAU == "") {
                            // tang MA_LOAI_DAU trong CSDL len 1
                            $db = new database();
                            $db->setQuery("SELECT MAX(MA_LOAI_DAU) FROM loai_dau");
                            $result = $db->fetchAll();
                            while ($rows = mysql_fetch_array($result)) {
                                $MA_LOAI_DAU = $rows['MAX(MA_LOAI_DAU)'];
                            }

                            $MA_LOAI_DAU = $MA_LOAI_DAU + 1;


                            //them MA_LOAI_DAU + TEN_LOAI_DAU moi VAO CSDL
                            // them vao bang loai_dau ----------------------------------------------------------------
                            $db = new database();
                            $db->setQuery("insert into loai_dau(MA_LOAI_DAU,TEN_LOAI_DAU) 
            values ('" . $MA_LOAI_DAU . "','" . $TEN_LOAI_DAU . "')");
                            $db->executeQuery();
                        }
                    }

                    // bang mba_tram
                    $TEN_TRAM = $val[13];
                    // lay MA_TRAM TU TEN_TRAM CO DUOC TU FILE EXCEL
                    $db = new database();
                    $db->setQuery("select * from tram");
                    $result = $db->fetchAll();
                    while ($rows = mysql_fetch_array($result)) {
                        if ($TEN_TRAM == $rows['TEN_TRAM']) {
                            $MA_TRAM = $rows['MA_TRAM'];
                        }
                    }
                    $NGAY_VAN_HANH = $val[14];
                    // BANG chi_tiet_so_huu
                    //lay MA_DON_VI TU TEN_DON_VI CO DUOC TU FILE EXCEL
                    $TEN_DON_VI = $val[33];

                    $db = new database();
                    $db->setQuery("select * from don_vi");
                    $result = $db->fetchAll();
                    while ($rows = mysql_fetch_array($result)) {
                        if ($TEN_DON_VI == $rows['TEN_DON_VI']) {
                            $MA_DON_VI = $rows['MA_DON_VI'];
                        }
                    }

                    $NGAY_CHUYEN_DEN = $val[34];

                    // gan cac thong tin tu 1 dong cua file excel vao 1 doi tuong -> them vao csdl
                    $MBA->setSO_N0($SO_N0);
                    $MBA->setMSTS($MSTS);
                    $MBA->setCONG_SUAT($CONG_SUAT);
                    $MBA->setMA_NSX($MA_NSX);
                    $MBA->setMA_LOAI($MA_LOAI);
                    $MBA->setCHIEU_DAI($CHIEU_DAI);
                    $MBA->setCHIEU_RONG($CHIEU_RONG);
                    $MBA->setCHIEU_CAO($CHIEU_CAO);
                    $MBA->setDAU_MAY($DAU_MAY);
                    $MBA->setRUOT_MAY($RUOT_MAY);
                    $MBA->setTONG($TONG);
                    $MBA->setNAM_SX($NAM_SX);
                    $MBA->setNAM_NHAP_VE($NAM_NHAP_VE);
                    $MBA->setTHONG_SO_DO($THONG_SO_DO);
                    $MBA->setNHAN_XET($NHAN_XET);
                    $MBA->setGHI_CHU($GHI_CHU);
                    $MBA->setMA_DIEN_AP($MA_DIEN_AP);
                    $MBA->setNGAY_DAI_TU($NGAY_DAI_TU);
                    $MBA->setNGAY_NGUNG_VH($NGAY_NGUNG_VH);
                    $MBA->setTEN_TINH_TRANG_MAY($TEN_TINH_TRANG_MAY);
                    $MBA->setTINH_TRANG_NGAY($TINH_TRANG_NGAY);
                    $MBA->setDG_TINH_TRANG($DG_TINH_TRANG);
                    $MBA->setMA_LOAI_DAU($MA_LOAI_DAU);
                    $MBA->setTEN_LOAI_DAU($TEN_LOAI_DAU);
                    $MBA->setTEN_TRAM($TEN_TRAM);
                    $MBA->setMA_TRAM($MA_TRAM);
                    $MBA->setNGAY_VAN_HANH($NGAY_VAN_HANH);
                    $MBA->setTEN_DON_VI($TEN_DON_VI);
                    $MBA->setMA_DON_VI($MA_DON_VI);
                    $MBA->setNGAY_CHUYEN_DEN($NGAY_CHUYEN_DEN);

                    if ($MBA->isExits("may_bien_ap", "SO_N0='" . $MBA->getSO_N0() . "'") <= 0) {
                        if ($MBA->themMBA() > 0) {
                            //echo " OK! ";
                        } else {
                            //	echo "Khong thanh cong";
                        }
                    } else {
                        //MBA da ton tai
                        //	echo "Bi trung so N0";
                    }
                }
                
                echo "<a class='DataDetail'>Số dòng đã thêm vào hệ thống: ";
                echo $sodongthemvao;
                echo "</a>";
                echo "<br>";
                echo "<a class='error'>Số dòng lỗi: ";
                echo $sodongloi;
                echo "<br>";
            } else {
                echo "Chọn file excel để upload!";
            }
        }
        ?>
    </body>
</html>



